# -*- coding: utf-8 -*-

import requests
import json
import re
import pymysql
from config import MYSQL_CONFIG
from pyquery import PyQuery as pq
from logger import logger
import time
from decimal import Decimal
import traceback
import math
import gevent

"""
采集的数据源是网易的股票数据
"""


def get_stock_categroy():
    """
    获取股票分类，全量更新
    :return:
    """
    conn = pymysql.connect(**MYSQL_CONFIG)
    cursor = conn.cursor()
    url = "http://quotes.money.163.com/old/#query=EQB&DataType=HS_RANK&sort=PERCENT&order=desc&count=24&page=0"
    req = requests.get(url)
    if req.status_code == 200:
        doc = pq(req.text)
        for cate in doc("#f0-f3 li").items():
            code = cate.attr("qid")
            label = cate("a").text()

            sql = "SELECT `id`, `STYPE`, `STYPE_LABEL` FROM `stock_cate` WHERE `STYPE`=%s"

            cursor.execute(sql, code)
            query = cursor.fetchall()
            if len(query) == 0:
                sql = "INSERT INTO `stock_cate`(`STYPE`, `STYPE_LABEL`) VALUES (%s,%s)"
                cursor.execute(sql, (code, label))
                conn.commit()

    conn.close()


def get_stock_dapan_spider():
    """
    获取股股大盘数据
    :return:
    """
    logger.info("==========大盘数据采集开始==========")
    conn = pymysql.connect(**MYSQL_CONFIG)
    cursor = conn.cursor()

    keys = ["STYPE", "CODE", "FIVE_MINUTE", "HIGH", "HS", "LB", "LOW", "MCAP", "MFSUM", "NAME", "OPEN", "PE", "PERCENT",
            "PRICE",
            "SYMBOL", "TCAP", "TURNOVER", "UPDOWN", "VOLUME", "WB", "YESTCLOSE", "ZF"]

    # 查询所有分类
    sql = "SELECT `STYPE`,`STYPE_LABEL` FROM `stock_cate`"
    cursor.execute(sql)
    query = cursor.fetchall()

    def main(url, cate, page):
        """
        执行函数
        :param url:
        :param params:
        :return:
        """
        parmas = {
            "host": "http://quotes.money.163.com/hs/service/diyrank.php",
            "page": page,  # 页码
            "query": "STYPE:" + cate,
            "SME": "true;",
            "NODEAL": "false",
            "fields": "NO,LB,SYMBOL, NAME, PRICE, PERCENT, UPDOWN, FIVE_MINUTE, OPEN, YESTCLOSE, HIGH, LOW, VOLUME, TURNOVER, HS, LB, WB, ZF, PE, MCAP, TCAP, MFSUM, MFRATIO.MFRATIO2, MFRATIO.MFRATIO10, SNAME, CODE, ANNOUNMT, UVSNEWS",
            "sort": "PERCENT",
            "order": "desc",
            "count": "2000",  # 看看是不是pagesize 是的 看来页码有限制
            "type": "query"
        }
        req = requests.get(url, params=parmas)
        if req.status_code == 200:
            jsondata = json.loads(req.text)
            for each in jsondata["list"]:
                each["STYPE"] = cate

            return jsondata["list"]

            # 准备插入数据
            # 他有两页数据 我们怎么做呢 好的 开

    data = []
    for cate in query:
        logger.info("当前采集分类:" + cate[1])
        for page in range(3):
            url = "http://quotes.money.163.com/hs/service/diyrank.php"
            ret = main(url, cate[0], page)
            data = data + ret

    arrtouple = []
    today = time.strftime("%Y-%m-%d", time.localtime())
    for item in data:
        subItem = []
        for key in keys:
            if key not in item:
                item[key] = None

            subItem.append(item[key])
        subItem.append(today)
        subItem = tuple(subItem)
        arrtouple.append(subItem)

    # 判断今天的大盘数据是否已经存在，不存在全部插入，存在则更新
    sql = "SELECT `Id` FROM `stock` WHERE `CTIME`='%s' LIMIT 1" % today
    cursor.execute(sql)
    query = cursor.fetchall()
    if len(query) > 0:
        # 数据已经存在
        logger.info("==========大盘数据开始更新==========")
        update = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        for item in data:
            sql = "UPDATE `stock_overview` SET `FIVE_MINUTE`={},`HIGH`={},`HS`={},`LB`={},`LOW`={},`MCAP`={},`MFSUM`={},`PE`={},`PERCENT`={},`PRICE`={},`TCAP`={},`TURNOVER`={},`UPDOWN`={},`VOLUME`={},`WB`={},`ZF`={},`UPDATE_TIME`='{}' WHERE `CODE`={} AND `CTIME`='{}'" \
                .format(item["FIVE_MINUTE"], item["HIGH"], item["HS"], item["LB"], item["LOW"], item["MCAP"],
                        item["MFSUM"], item["PE"], item["PERCENT"], item["PRICE"], item["TCAP"], item["TURNOVER"],
                        item["UPDOWN"], item["VOLUME"], item["WB"], item["ZF"], update, item["CODE"], today)
            sql = sql.replace("None", "null")
            try:
                cursor.execute(sql)
                conn.commit()
            except:
                logger.error(traceback.format_exc())
                logger.error(sql)
        logger.info("==========大盘数据更新完成==========")

    else:
        # 查看是否有新股数据,替换更新
        # for item in arrtouple:
        #     sql = "REPLACE INTO `stock`(`CODE`, `NAME`) VALUES (%s,%s)"
        #     cursor.execute(sql, (item[1], item[9]))
        #     conn.commit()

        # 数据不存在
        vals = re.sub(r"[\[\]]", "", str(arrtouple))
        sql = "INSERT INTO `stock_overview`(`STYPE`,`CODE`, `FIVE_MINUTE`, `HIGH`, `HS`, `LB`, `LOW`, `MCAP`, `MFSUM`, `NAME`, `OPEN`, `PE`, `PERCENT`, `PRICE`, `SYMBOL`, `TCAP`, `TURNOVER`, `UPDOWN`, `VOLUME`, `WB`, `YESTCLOSE`, `ZF`,`CTIME`) VALUES %s" % vals
        sql = sql.replace("None", "null")
        cursor.execute(sql)
        conn.commit()
        logger.info("==========大盘数据采集完成==========")

    conn.close()


def get_stock_history_data():
    """
    获取股票的历史交易数据
    :return:
    """
    logger.info("==========获取股票的历史交易数据==========")
    conn = pymysql.connect(**MYSQL_CONFIG)
    cursor = conn.cursor()

    sql = "SELECT `Id`, `CODE`, `NAME`, `CTIME` FROM `stock` WHERE 1"
    cursor.execute(sql)

    query = cursor.fetchall()

    # 这个函数跑的慢 用协程加速

    def gavent_function(code):
        logger.info("当前获取ID:{}".format(code))
        url = "http://quotes.money.163.com/trade/lsjysj_{}.html#01b07".format(code)
        req = requests.get(url)
        if req.status_code == 200:
            doc = pq(req.content)
            data_lists = []
            for index, tr in enumerate(doc(".limit_sale tr").items()):
                if index > 0:
                    try:
                        tr_data = (
                            code,
                            tr("td").eq(0).text(),
                            float(tr("td").eq(1).text()),
                            float(tr("td").eq(2).text()),
                            float(tr("td").eq(3).text()),
                            float(tr("td").eq(4).text()),
                            float(tr("td").eq(5).text()),
                            float(tr("td").eq(6).text()),
                            float(tr("td").eq(7).text().replace(",", "")),
                            float(tr("td").eq(8).text().replace(",", "")),
                            float(tr("td").eq(9).text()),
                            float(tr("td").eq(10).text())
                        )
                        sql = "SELECT `ID` FROM `stock_history_trade` WHERE `CODE`={} AND `DATA_DATE`='{}'".format(code,
                                                                                                                   tr(
                                                                                                                       "td").eq(
                                                                                                                       0).text())
                        cursor.execute(sql)
                        if cursor.fetchone() is None:
                            # 单次插入避免重复
                            sql = "INSERT INTO `stock_history_trade`(`CODE`,`DATA_DATE`, `OPEN`, `HIGH`, `LOW`, `CLOSE`, `UPDOWN`, `PERCENT`, `VOLUME`, `TURNOVER`, `ZF`, `HS`) VALUES {}".format(
                                str(tr_data))
                            cursor.execute(sql)
                            conn.commit()
                    except:
                        logger.error(traceback.format_exc())

    pools = []
    for stock in query:
        code = stock[1]
        pools.append(gevent.spawn(gavent_function, code))

    gevent.joinall(pools)


def get_stock_timely():
    """
    获取股票的及时数据
    :return:
    """
    logger.info("==========获取股票的及时数据==========")
    conn = pymysql.connect(**MYSQL_CONFIG)
    cursor = conn.cursor()

    sql = "SELECT `Id`, `CODE`, `NAME`, `CTIME` FROM `stock` WHERE 1"
    cursor.execute(sql)

    query = cursor.fetchall()

    codes = []
    for stock in query:
        codes.append(stock[1])

    # 每次可以取1000
    num = 0
    for i in range(math.ceil(len(codes) / 1000)):
        start = i * 1000
        end = (i + 1) * 1000

        codes_str = str(codes[start:end])[1:-1].replace(" ", "")
        url = "http://api.money.126.net/data/feed/{},money.api?callback=_ntes_quote_callback{}".format(codes_str,
                                                                                                       int(time.time()))
        req = requests.post(url)
        if req.status_code == 200:
            data_str = re.findall("_ntes_quote_callback\d+\((.*?)\);", req.text)[0]
            json_data = json.loads(data_str)
            sql_data = []
            for key in json_data.keys():
                if "price" in json_data[key]:
                    sql_data.append((
                        key,
                        json_data[key]["price"],
                        json_data[key]["open"],
                        json_data[key]["yestclose"],
                        json_data[key]["high"],
                        json_data[key]["low"],
                        json_data[key]["updown"],
                        json_data[key]["percent"],
                        json_data[key]["volume"],
                        json_data[key]["turnover"],
                        json_data[key]["time"].replace("/", "-"),
                    ))
            num += len(sql_data)

            sql = "INSERT INTO `stock_timely`(`CODE`,`PRICE`,`OPEN`, `YESTCLOSE`, `HIGH`, `LOW`, `UPDOWN`, `PERCENT`, `VOLUME`, `TURNOVER`,`TIME`) VALUES %s" % str(
                sql_data)[1:-1]
            cursor.execute(sql)
            conn.commit()
    conn.close()
    logger.info(
        "=========={} {}只股票的及时数据获取成功==========".format(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), num))


if __name__ == "__main__":
    pass


    # get_stock_dapan_spider()
    # get_stock_categroy()
    # get_stock_history_data()
    # get_stock_timely()

    def f1(x):
        return x


    def f2():
        for i in range(10):
            a = f1(i)


    f2()
